package com.wuye.repository;

import com.wuye.model.repair.RepairPerson;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * Created by jiawenlong on 2016/7/26.
 */
public interface RepairPersonRepository extends PagingAndSortingRepository<RepairPerson, Long> {
    RepairPerson findById(long id);
    List<RepairPerson> findAll();

    @Query(nativeQuery = true, value = "select p.id,p.name,p.mobile,c.name as categroryName,p.post,p.categoryId from RepairPerson p LEFT JOIN Category c on p.categoryId=c.id where " +
            " IF(:qf = '', 1=1, IF(:qf = 'name', p.name LIKE %:qv%, IF(:qf = 'mobile', p.mobile LIKE %:qv%,0=1) ) )  " +
            " AND IF(:categoryId = -1,1=1,c.id= :categoryId)" +
            "  order by id desc LIMIT :st, :ps")
    List<Object[]> queryUser(@Param("qf") String qField, @Param("qv") String qVal,
                             @Param("st") int start, @Param("ps") int pageSize, @Param("categoryId") long categoryId);

    @Query(nativeQuery = true, value = "SELECT COUNT(DISTINCT p.id) from RepairPerson p LEFT JOIN Category c on p.categoryId=c.id where " +
            " IF(:qf = '', 1=1, IF(:qf = 'name', p.name LIKE %:qv%, IF(:qf = 'mobile', p.mobile LIKE %:qv%,0=1) ) ) " +
            " AND IF(:categoryId = -1,1=1,c.id= :categoryId)")
    int queryUserCount(@Param("qf") String qField, @Param("qv") String qVal, @Param("categoryId") long categoryId);
}
